<?php 
include_once 'excel_head.php'; 

if (isset($_POST['fd'] , $_POST['fh'], $_POST['es'])) {
    $fechaDesde = $_POST['fd']; 
    $fechaHasta = $_POST['fh']; 
    $estados = $_POST['es']; 
} else {
    // The correct POST variables were not sent to this page. 
    header('Location: main.php?pag=re');
}

function empresaSesion() {
	return $_SESSION['empresa'];
}

// New Connection
$db = new mysqli(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME);

// Check for errors
if(mysqli_connect_errno()){
 echo mysqli_connect_error();
}

$reporte->setActiveSheetIndex(0)->setCellValue("F2","ORDENES DE TRABAJO"); 
$reporte->setActiveSheetIndex(0)->setCellValue("F3","Fecha Desde: ".$fechaDesde." - Fecha Hasta: ".$fechaHasta." - Estados: ".$estados); 

$fila = 9;

$reporte->setActiveSheetIndex(0)->setCellValue("B$fila","Nro Lote"); 
$reporte->setActiveSheetIndex(0)->setCellValue("C$fila","Fecha Ingreso"); 
$reporte->setActiveSheetIndex(0)->setCellValue("D$fila","Nro OT"); 
$reporte->setActiveSheetIndex(0)->setCellValue("E$fila","Direccion"); 
$reporte->setActiveSheetIndex(0)->setCellValue("F$fila","Localidad"); 
$reporte->setActiveSheetIndex(0)->setCellValue("G$fila","Fecha Estado");
$reporte->setActiveSheetIndex(0)->setCellValue("H$fila","Estado");  
$reporte->setActiveSheetIndex(0)->setCellValue("I$fila","Nro Reingreso"); 
$reporte->setActiveSheetIndex(0)->setCellValue("J$fila","Con Vallas"); 
$reporte->setActiveSheetIndex(0)->setCellValue("K$fila","Dias Vallas"); 
$reporte->setActiveSheetIndex(0)->setCellValue("L$fila","Total"); 
$reporte->setActiveSheetIndex(0)->setCellValue("M$fila","Nro Certificacion"); 
$reporte->setActiveSheetIndex(0)->setCellValue("N$fila","Monto Certificacion"); 
$reporte->setActiveSheetIndex(0)->setCellValue("O$fila","Observaciones"); 
$reporte->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$reporte->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);

for ( $i = 1 ; $i < 15 ; $i ++) {
			
	$reporte->getActiveSheet()
	    ->getStyleByColumnAndRow($i, $fila)
	    ->getFont()->applyFromArray($style['label']);
				
	$reporte->getActiveSheet()
	    ->getStyleByColumnAndRow($i, $fila)
			->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
				
	$reporte->getActiveSheet()
	    ->getStyleByColumnAndRow($i, $fila)
			->getFill()->getStartColor()->setRGB('E4EAF4');
			
}

$fila = $fila + 1;

$result = $db->query("SELECT NRO_LOTE, NRO_ORDEN_TRABAJO, FECHA_RECEPCION, NRO_REINGRESO, DIRECCION, LOCALIDAD, FECHA_ESTADO, ESTADO, VALLAS, CANTIDAD_DIAS_VALLAS, COSTO_TOTAL_OT, NRO_CERTIFICADO, MONTO_CERTIFICADO, OBSERVACIONES FROM serverinstall.ordenes_trabajo WHERE empresa = ".empresaSesion()." and estado in (".$estados.") AND fecha_estado >= '".$fechaDesde."' AND fecha_estado <= '".$fechaHasta."';");

if($result){
     // Cycle through results
    while ($row = $result->fetch_array()){
			$ots[] = $row;
    }
    // Free result set
    $result->close();
    //$db->next_result();
}

foreach ($ots as $ot) {
		$reporte->setActiveSheetIndex(0)->setCellValue("B$fila",$ot['NRO_LOTE']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("C$fila",$ot['FECHA_RECEPCION']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("D$fila",$ot['NRO_ORDEN_TRABAJO']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("E$fila",$ot['DIRECCION']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("F$fila",$ot['LOCALIDAD']);  								
		$reporte->setActiveSheetIndex(0)->setCellValue("G$fila",$ot['FECHA_ESTADO']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("H$fila",$ot['ESTADO']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("I$fila",$ot['NRO_REINGRESO']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("J$fila",$ot['VALLAS']);  
		$reporte->setActiveSheetIndex(0)->setCellValue("K$fila",$ot['CANTIDAD_DIAS_VALLAS']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("L$fila",$ot['COSTO_TOTAL_OT']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("M$fila",$ot['NRO_CERTIFICADO']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("N$fila",$ot['MONTO_CERTIFICADO']);  						
		$reporte->setActiveSheetIndex(0)->setCellValue("O$fila",$ot['OBSERVACIONES']);  						

		$fila = $fila + 1;
}

$fila = $fila + 1;

// Close connection
$db->close();

$objWriter = PHPExcel_IOFactory::createWriter($reporte, 'Excel2007'); 

ob_end_clean();

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ordenes_x_estados.xlsx"');
header('Cache-Control: max-age=0'); 	

$objWriter->save('php://output');
?>